import logging
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_rec_head_router = APIRouter(prefix="/container/recHead", tags=["容器入库管理"])


class ContainerInquiryQueryParams(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    ftakedateStart: str = Field("", description="开始日期(yyyyMMdd)")
    ftakedateEnd: str = Field("", description="结束日期(yyyyMMdd)")
    forduser: str = Field("", description="订单用户")
    fstockno: str = Field("", description="库存编号")
    fbillno: str = Field("", description="单据编号")
    fbillstate: str = Field("", description="单据状态")


@data_rec_head_router.get("/list", summary="容器入库查询接口")
def list_rec_head(container: ContainerInquiryQueryParams = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_pack_rec_head where 1=1"
    if container.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = container.fsiteNo
    if container.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = container.fbillno
    if container.forduser != "":
        sql = sql + " and forduser = :forduser "
        param["forduser"] = container.forduser
    if container.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = container.fstockno
    if container.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = container.fbillstate
    if (container.ftakedateStart != "") and (container.ftakedateEnd != ""):
        sql = sql + " and to_char(forddate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = container.ftakedateStart
        param["ftakedateEnd"] = container.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fstockno,fsite_no,forduser,forddate,"
    sql = sql + " fplanrecdate,frecdate,fsupno,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fsrcbillno,fexplanation,fupddate,foperator,"
    sql = sql + " fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from i_pack_rec_head where 1=1"
    if container.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = container.fsiteNo
    if container.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = container.fbillno
    if container.forduser != "":
        sql = sql + " and forduser = :forduser "
        param["forduser"] = container.forduser
    if container.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = container.fstockno
    if container.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = container.fbillstate
    if (container.ftakedateStart != "") and (container.ftakedateEnd != ""):
        sql = sql + " and to_char(forddate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = container.ftakedateStart
        param["ftakedateEnd"] = container.ftakedateEnd
    sql = sql + " limit :limit offset :offset "
    param["offset"] = container.offset
    param["limit"] = container.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpackno,b.fbuyprice,b.fqty,b.fprocdate,b.ftotamt,b.fmemo,c.fpackname"
    sql_3 = sql_3 + " from i_pack_rec_head a,i_pack_rec_detail b,i_ts_pack c "
    sql_3 = sql_3 + " where a.fbillno=b.fbillno "
    sql_3 = sql_3 + " and b.fpackno=c.fpackno"
    if container.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = container.fsiteNo
    if container.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = container.fbillno
    if container.forduser != "":
        sql_3 = sql_3 + " and a.forduser = :forduser "
        param["forduser"] = container.forduser
    if container.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = container.fstockno
    if container.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = container.fbillstate
    if (container.ftakedateStart != "") and (container.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.forddate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = container.ftakedateStart
        param["ftakedateEnd"] = container.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        forddate = ""
        try:
            forddate = data[key][4].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (forddate == ""):
            forddate = None

        fplanrecdate = ""
        try:
            fplanrecdate = data[key][5].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fplanrecdate == ""):
            fplanrecdate = None

        frecdate = ""
        try:
            frecdate = data[key][6].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (frecdate == ""):
            frecdate = None

        fupddate = ""
        try:
            fupddate = data[key][13].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        fcheckdate = ""
        try:
            fcheckdate = data[key][15].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fstockno'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['forduser'] = data[key][3]
        d1['forddate'] = forddate
        d1['fplanrecdate'] = fplanrecdate
        d1['frecdate'] = frecdate
        d1['fsupno'] = data[key][7]
        d1['fitemcount'] = data[key][8]
        d1['fpiececount'] = data[key][9]
        d1['ftotalamt'] = data[key][10]
        d1['fsrcbillno'] = data[key][11]
        d1['fexplanation'] = data[key][12]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][14]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][16]
        d1['fbillstate'] = data[key][17]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpackno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['fprocdate'] = data_detail[j1][5]
                d2['ftotamt'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                d2['fpackname'] = data_detail[j1][8]
                detail_list.append(d2)

        d1["details"] = detail_list
        detail_list2 = []
        d1["details2"] = detail_list2
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class ContainerInquiryDetail(BaseModel):
    """容器入库明细"""
    fline: int = Field(..., description="行号")
    fpackno: str = Field(..., description="包装编号")
    fpackname: str = Field(..., description="包装名称")
    fbuyprice: float = Field(0.0, description="购买价格，默认0.0")
    fqty: float = Field(1.0, description="数量，默认1.0")
    ftotamt: float = Field(0.0, description="总金额，默认0.0")
    fmemo: str = Field("", description="备注，默认空字符串")
    fprocdate: Optional[str] = Field(None, description="处理日期(yyyy-MM-ddTHH:mm:ss)")


class ContainerCreate(BaseModel):
    """容器入库新增请求"""
    fbillno: str = Field(..., description="单据编号")
    forddate: str = Field(..., description="订单日期(yyyy-MM-ddTHH:mm:ss)")
    forduser: str = Field(..., description="订单用户")
    fplanrecdate: str = Field(..., description="计划入库日期(yyyy-MM-ddTHH:mm:ss)")
    fsiteNo: str = Field(..., description="站点编号")
    fstockno: str = Field(..., description="库存编号")
    frecdate: str = Field(..., description="实际入库日期(yyyy-MM-ddTHH:mm:ss)")
    fexplanation: str = Field("0", description="入库说明，默认'0'")
    fsrcbillno: str = Field("0", description="源单据编号，默认'0'")
    fsupno: str = Field("", description="供应商编号，默认空字符串")
    details: List[ContainerInquiryDetail] = Field(..., description="入库明细列表")


@data_rec_head_router.post("/create", summary="容器入库新增接口")
def create_rec_head(container: ContainerCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0.0
        fbillno = container.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = container.details
            for data in jsondata:
                sql = "insert into i_pack_rec_detail(fbillno,fline,fpackno,fbuyprice,fqty,ftotamt,fmemo"
                if data.fprocdate is not None:
                    sql = sql + ",fprocdate"
                sql = sql + ")"
                sql = sql + " values( :fbillno , :fline , :fpackno , :fbuyprice , :fqty , :ftotamt , :fmemo "
                if data.fprocdate is not None:
                    sql = sql + ", :fprocdate "
                sql = sql + ");"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpackno": data.fpackno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fmemo": data.fmemo,
                    "fprocdate": data.fprocdate,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + int(int(data['fqty']))
                ftotalamt = ftotalamt + float(data.ftotamt)

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fpackno'] = data.fpackno
                d2['fpackname'] = data.fpackname
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = data.fmemo
                d2['fprocdate'] = data.fprocdate
                detail_list.append(d2)
            sql = "insert into i_pack_rec_head(fbillno,fsite_no,forduser,forddate,fplanrecdate,fstockno,fsupno,fitemcount,fpiececount,ftotalamt,fsrcbillno,fexplanation,frecdate,fupddate,foperator,fbillstate)"
            sql = sql + (
                " values( :fbillno , :fsiteNo , :forduser , :forddate , :fplanrecdate , :fstockno , :fsupno , :fitemcount ,"
                " :fpiececount , :ftotalamt , :fsrcbillno , :fexplanation , :frecdate ,now(), :forduser ,1)")
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": container.fsiteNo,
                "forduser": container.forduser,
                "forddate": container.forddate,
                "fplanrecdate": container.fplanrecdate,
                "fstockno": container.fstockno,
                "fsupno": container.fsupno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fsrcbillno": container.fsrcbillno,
                "fexplanation": container.fexplanation,
                "frecdate": container.frecdate,
            })

            d1['fbillno'] = fbillno
            d1['fsiteno'] = container.fsiteNo
            d1['forduser'] = container.forduser
            d1['forddate'] = container.forddate
            d1['fplanrecdate'] = container.fplanrecdate
            d1['fstockno'] = container.fstockno
            d1['fsupno'] = container.fsupno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = container.fexplanation
            d1['fupddate'] = container.fplanrecdate
            d1['frecdate'] = container.frecdate
            d1['foperator'] = container.forduser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1["details"] = detail_list
            detail_list2 = []
            d1["details2"] = detail_list2
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_rec_head_router.post("/update", summary="容器入库修改接口")
def update_rec_head(container: ContainerCreate, db: Session = Depends(get_db), token: Session = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0.0
        fbillno = container.fbillno
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from i_pack_rec_detail where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            jsondata = container.details
            for data in jsondata:
                sql = "insert into i_pack_rec_detail(fbillno,fline,fpackno,fbuyprice,fqty,ftotamt,fmemo"
                if data.fprocdate is not None:
                    sql = sql + ",fprocdate"
                sql = sql + ")"
                sql = sql + " values( :fbillno , :fline , :fpackno , :fbuyprice , :fqty , :ftotamt , :fmemo "
                if data.fprocdate is not None:
                    sql = sql + ", :fprocdate "
                sql = sql + ");"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpackno": data.fpackno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fmemo": data.fmemo,
                    "fprocdate": data.fprocdate,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + float(data['fqty'])
                ftotalamt = ftotalamt + float(data.ftotamt)
            sql = "update i_pack_rec_head "
            sql = sql + " set fstockno= :fstockno ,fsupno= :fsupno ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fstockno": container.fstockno,
                "fsupno": container.fsupno,
                "fexplanation": container.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": userid,
                "fbillno": fbillno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_rec_head_router.post("/delete/{fbillno}", summary="容器入库删除接口")
def delete_rec_head(fbillno: str = Path(description="入库单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from i_pack_rec_head where fbillno= :fbillno and fbillstate>1"
            data = db.execute(text(sql), {
                "fbillno": fbillno,
            }).fetchall()
            if data[0][0] > 0:
                return BaseResponse.error(message="该订单不能删除")
            else:
                sql = "delete from i_pack_rec_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from i_pack_rec_head where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
